package com.sl.au.repository;

import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import com.sl.au.entity.Grading;
import com.sl.au.entity.Part;


@Transactional(readOnly = true)
public interface GradingRepository extends JpaRepository<Grading, String> {
	
	@Query(" select a from Grading a where a.gradingParent is not null order by a.gradingLevel.extended1 asc,a.gradingOrder asc")
	List<Grading> findAllNodes();
	
	@Query(" select a from Grading a where a.gradingParent is null and ( a.gradingName like %?1% or a.gradingIdentify like %?1% or a.gradingLevel.title like %?1%) ")
	Page<Grading> getGradingByKey(String key, Pageable pageable);
	
	@Query(" select count(*) from Grading a where a.gradingParent is null and ( a.gradingName like %?1% or a.gradingIdentify like %?1% or a.gradingLevel.title like %?1%) ")
	long getGradingByKeyCount(String key);
	
	@Query(" select a from Grading a where a.gradingParent is null ")
	Page<Grading> getGrading(Pageable pageable);
	
	@Query(" select a from Grading a where a.gradingParent is not null ")
	List<Grading> getBindingGrading();
	
	@Query(" select count(*) from Grading a where a.gradingParent is null ")
	long getGradingCount();
	
	@Modifying
	@Query("update Grading a set a.gradingParent = null where a.id=?1")
	int setGrading(String id);
	
	@Query(" select IFNULL(max(a.gradingOrder),-2) + 2 from Grading a where a.gradingParent = ?1 ")
	int getGradingOrder(String parentId);
	
	@Query(" select a from Grading a where a.gradingParent = ?1 order by a.gradingOrder asc")
	List<Grading> getGradingsByParent(String parentId);
	
	Grading findTopByGradingParentAndGradingIdentify(String gradingParent, String gradingIdentify);
	
	Grading findTopByGradingParent(String gradingParent);
	
	Page<Grading> findByGradingParent(String gradingParent,Pageable pageable);
	
	@Query(" select a from Grading a where a.gradingParent = ?1 and ( a.gradingName like %?2% or a.gradingIdentify like %?2% or a.gradingLevel.title like %?2%) ")
	Page<Grading> findByGradingParentAndKey(String id ,String key, Pageable pageable);
	
	@Query(" select a from Grading a order by a.gradingLevel.extended1 asc")
	List<Grading> findGradingAll();
}
